package data.deal;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import util.DateUtil;
import dataconnect.ConnectionFactory;

public class CorrectAmountBat {
	/*public CorrectAmountBat() throws SQLException {
		String sql = "select dealday from stockdaydeal where dealday>'2013-01-01' GROUP BY dealday order by dealday";
		Connection conn = ConnectionFactory.getConnection();
		Statement stmt = conn.createStatement();
		Statement stmt2 = conn.createStatement();
		ResultSet rs = stmt2.executeQuery(sql);
		boolean isFirst = true;
		Date preDate = null;
		Date thisDate;
		while(rs.next()){
			if(isFirst){
				preDate = rs.getDate(1);
				isFirst = false;
				continue;
			}else{
				thisDate = rs.getDate(1);
			}
			System.out.println(thisDate.toString());
			stmt.addBatch("CREATE table ttt select t1.id,ROUND(t1.amount/t2.amount,2) as amountBat "
					+ "from stockdaydeal t1 join stockdaydeal t2 on t1.number = t2.number "
					+ "and t1.dealDay = '"+thisDate.toString()+"' AND t2.dealday = '"+preDate.toString()+"'");
			stmt.addBatch("create UNIQUE INDEX xxx on ttt(id)");
			stmt.addBatch("update stockdaydeal t set amountBat = (select amountBat from ttt where t.id = ttt.id ) where EXISTS (SELECT 1 from ttt where t.id = ttt.id)");
			stmt.addBatch("drop table ttt");
			stmt.executeBatch();
			preDate = rs.getDate(1);
		}
	}*/
	
	public CorrectAmountBat() throws SQLException {
		String sql = "select number from stockdaydeal where number>'sz002496' GROUP BY number";
		Connection conn = ConnectionFactory.getConnection();
		Statement stmt = conn.createStatement();
		Statement stmt2 = conn.createStatement();
		ResultSet rs = stmt2.executeQuery(sql);
		List<String> codes = new ArrayList<String>();
		while(rs.next()){
			codes.add(rs.getString(1));
		}
		rs.close();
		rs = null;
		for (String code : codes) {
			boolean isFirst = true;
			BigDecimal preAmount = null;
			BigDecimal amount;
			rs = stmt2.executeQuery("select amount,id from stockdaydeal where number = '"+code+"' and qty<>0 order by dealDay");
			conn.setAutoCommit(false);
			while (rs.next()) {
				if(isFirst){
					preAmount=rs.getBigDecimal(1);
					isFirst=false;
					continue;
				}else{
					amount = rs.getBigDecimal(1);
				}
				BigDecimal amountBat = amount.divide(preAmount,2,BigDecimal.ROUND_HALF_UP);
				if(amountBat.compareTo(new BigDecimal("999"))>0){
					amountBat = new BigDecimal("999");
				}
				stmt.addBatch("update stockdaydeal set amountBat = "+amountBat+" where id ='"+rs.getString(2)+"'");
				preAmount=rs.getBigDecimal(1);
			}
			stmt.executeBatch();
			conn.commit();
			stmt.clearBatch();
			rs.close();
			rs = null;
			System.out.println(code);
		}
		conn.setAutoCommit(true);
	}
	
	public CorrectAmountBat(Date date) throws SQLException {
		long t = System.currentTimeMillis();
		Connection conn = ConnectionFactory.getConnection();
		Statement stmt = conn.createStatement();
		Date preDate = DateUtil.getLastDealDate(date);
		stmt.executeUpdate("CREATE table ttt select t1.id,ROUND(t1.amount/t2.amount,2) as amountBat "
				+ "from stockdaydeal t1 join stockdaydeal t2 on t1.number = t2.number "
				+ "and t1.dealDay = '"
				+ date.toString()
				+ "' AND t2.dealday = '" + preDate.toString() + "'");
		stmt.addBatch("create UNIQUE INDEX xxx on ttt(id)");
		stmt.addBatch("update stockdaydeal t set amountBat = (select amountBat from ttt where t.id = ttt.id ) where EXISTS (SELECT 1 from ttt where t.id = ttt.id)");
		stmt.addBatch("drop table ttt");
		stmt.executeBatch();
		stmt.close();
		long t2 = System.currentTimeMillis();
		System.out.println("correct:"+(t2-t));
	}
	
	public static void main(String[] args) throws SQLException {
		new CorrectAmountBat();
	}
}
